#!/bin/bash

if [ -n "$2" ]
then
	do_date=$2
else
	#echo 必须给我传一个日期
	#exit
	do_date= $(date -d yesterday +%F) 
fi

echo 当前要操作的日期是$do_date

dws_paper_score_page_nd="
	insert overwrite table dws_paper_score_page_nd partition (dt='$do_date')
	select
		paper_id,
		paper_title,
		page_score,
		score_page_1d,
		sum(if(dt>=date_sub('$do_date',6), score_page_1d,0)) score_page_7d,
		sum(score_page_1d) score_page_30d
	from dws_paper_score_page_1d where dt<='$do_date' and dt>=date_sub('$do_date',29)
	group by
		paper_id,
		paper_title,dt,page_score,score_page_1d;
"
dws_question_accuracy_nd="
	insert overwrite table dws_question_accuracy_nd partition (dt='$do_date')
	select
		question_id,
		question_accuracy_1d,
		avg(if(dt>=date_sub('$do_date',6), question_accuracy_1d,0)) score_page_7d,
		avg(question_accuracy_1d) score_page_30d
	from dws_question_accuracy_1d where dt<='$do_date' and dt>=date_sub('$do_date',29)
	group by  question_id,question_accuracy_1d;
"

dws_trade_province_order_nd="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_province_order_nd partition (dt='$do_date')
select
    province_id,
    province_name,
    sum(if(dt>=date_sub('$do_date',6),order_users_num_1d,0)) order_count_7d,
    sum(if(dt>=date_sub('$do_date',6),order_count_1d,0)) order_original_amount_7d,
    sum(if(dt>=date_sub('$do_date',6),order_total_amount_1d,0)) activity_reduce_amount_7d,
    sum(if(dt>=date_sub('$do_date',29),order_users_num_1d,0)) order_count_29d,
    sum(if(dt>=date_sub('$do_date',29),order_count_1d,0)) order_original_amount_29d,
    sum(if(dt>=date_sub('$do_date',29),order_total_amount_1d,0)) activity_reduce_amount_29d
from dws_trade_province_order_1d
where dt <= '$do_date' and dt >= date_sub('$do_date',29)
group by province_id, province_name;
"

dws_trade_all_order_nd="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dws_trade_all_order_nd partition (dt='$do_date')
select
    sum(if(dt>=date_sub('$do_date',6),order_users_num_1d,0)) order_count_7d,
    sum(if(dt>=date_sub('$do_date',6),order_count_1d,0)) order_original_amount_7d,
    sum(if(dt>=date_sub('$do_date',6),order_total_amount_1d,0)) activity_reduce_amount_7d,
    sum(if(dt>=date_sub('$do_date',29),order_users_num_1d,0)) order_count_29d,
    sum(if(dt>=date_sub('$do_date',29),order_count_1d,0)) order_original_amount_29d,
    sum(if(dt>=date_sub('$do_date',29),order_total_amount_1d,0)) activity_reduce_amount_29d
from dws_trade_all_order_1d
where dt <= '$do_date' and dt >= date_sub('$do_date',29);
"

dws_trffic_category_user_videoplay_nd="
insert overwrite table dws_trffic_category_user_videoplay_nd partition (dt = '$do_date')
select
    category_id,
    category_name,
    user_id,
    sum(if(dt>=date_sub('$do_date',6), videoplay_count_1d,0)) videoplay_count_7d,
    sum( videoplay_count_1d) videoplay_count_30d
from dws_trffic_category_user_videoplay_1d
where dt<='$do_date' and dt>=date_sub('$do_date',29)
group by category_id, category_name, user_id;
"

dws_trffic_subject_user_videoplay_nd="
insert overwrite table dws_trffic_subject_user_videoplay_nd partition (dt = '$do_date')
select
    subject_id,
    subject_name,
    category_id,
    category_name,
    user_id,
    sum(if(dt>=date_sub('$do_date',6), videoplay_count_1d,0)) videoplay_count_7d,
    sum( videoplay_count_1d) videoplay_count_30d
from dws_trffic_subject_user_videoplay_1d
where dt<='$do_date' and dt>=date_sub('$do_date',29)
group by subject_id, subject_name, category_id, category_name, user_id;
"

dws_trffic_course_user_videoplay_nd="
insert overwrite table dws_trffic_course_user_videoplay_nd partition (dt = '$do_date')
select
    course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name,
    user_id,
    sum(if(dt>=date_sub('$do_date',6), videoplay_count_1d,0)) videoplay_count_7d,
    sum( videoplay_count_1d) videoplay_count_30d
from dws_trffic_course_user_videoplay_1d
where dt<='$do_date' and dt>=date_sub('$do_date',29)
group by course_id, course_name, subject_id, subject_name, category_id, category_name, user_id;
"

dws_trade_course_category_order_nd="
insert overwrite table dws_trade_course_category_order_nd partition(dt = '$do_date')
select
    category_id,
    category_name,
    sum(if(dt >= date_add('$do_date',-6),order_count_1d,0)) order_count_7d,
    sum(if(dt >= date_add('$do_date',-6),order_num_1d,0)) order_num_7d,
    sum(if(dt >= date_add('$do_date',-6),order_original_amount_1d,0)) order_original_amount_7d,
    sum(if(dt >= date_add('$do_date',-6),coupon_reduce_amount_1d,0)) coupon_reduce_amount_7d,
    sum(if(dt >= date_add('$do_date',-6),order_total_amount_1d,0)) order_total_amount_7d,
    sum(order_count_1d) order_count_30d,
    sum(order_num_1d) order_num_30d,
    sum(order_original_amount_1d) order_original_amount_30d,
    sum(coupon_reduce_amount_1d) coupon_reduce_amount_30d,
    sum(order_total_amount_1d) order_total_amount_30d
from dws_trade_course_category_order_1d
where dt >= date_add('$do_date',-29)
group by category_id, category_name;
"
## 2、dws_trade_subject_course_order_nd
dws_trade_subject_course_order_nd="
insert overwrite table dws_trade_subject_course_order_nd partition(dt = '$do_date')
select
    subject_id,
    subject_name,
    sum(if(dt >= date_add('$do_date',-6),order_count_1d,0)) order_count_7d,
    sum(if(dt >= date_add('$do_date',-6),order_num_1d,0)) order_num_7d,
    sum(if(dt >= date_add('$do_date',-6),order_original_amount_1d,0)) order_original_amount_7d,
    sum(if(dt >= date_add('$do_date',-6),coupon_reduce_amount_1d,0)) coupon_reduce_amount_7d,
    sum(if(dt >= date_add('$do_date',-6),order_total_amount_1d,0)) order_total_amount_7d,
    sum(order_count_1d) order_count_30d,
    sum(order_num_1d) order_num_30d,
    sum(order_original_amount_1d) order_original_amount_30d,
    sum(coupon_reduce_amount_1d) coupon_reduce_amount_30d,
    sum(order_total_amount_1d) order_total_amount_30d
from dws_trade_subject_course_order_1d
where dt >= date_add('$do_date',-29)
group by subject_id, subject_name;
"

dws_play_user_course_count_nd="
insert overwrite table dws_play_user_course_count_nd partition (dt = '$do_date')
select
       uid,id,chapter_name,course_name,play_count_1d,avg_duration_sec_1d,uv_count_1d,
    sum(if(dt>=date_sub('$do_date',6),play_count_1d,0)) order_count_7d,
    sum(if(dt>=date_sub('$do_date',6),avg_duration_sec_1d,0)) order_num_7d,
    sum(if(dt>=date_sub('$do_date',6),uv_count_1d,0)) order_original_amount_7d,
    sum(play_count_1d) activity_reduce_amount_30d,
    sum(avg_duration_sec_1d) coupon_reduce_amount_30d,
    sum(uv_count_1d) order_total_amount_30d
from dws_play_user_course_count_1d
where dt <= '$do_date' and dt >= date_sub('$do_date',29)
group by uid, id, chapter_name, course_name, play_count_1d, avg_duration_sec_1d, uv_count_1d;
"

dws_play_user_chapter_count_nd="
insert overwrite table dws_play_user_chapter_count_nd partition (dt = '$do_date')
select
       uid,id,chapter_name,course_name,play_count_1d,avg_duration_sec_1d,uv_count_1d,
    sum(if(dt>=date_sub('$do_date',6),play_count_1d,0)) order_count_7d,
    sum(if(dt>=date_sub('$do_date',6),avg_duration_sec_1d,0)) order_num_7d,
    sum(if(dt>=date_sub('$do_date',6),uv_count_1d,0)) order_original_amount_7d,
    sum(play_count_1d) activity_reduce_amount_30d,
    sum(avg_duration_sec_1d) coupon_reduce_amount_30d,
    sum(uv_count_1d) order_total_amount_30d
from dws_play_user_chapter_count_1d
where dt <= '$do_date' and dt >= date_sub('$do_date',29)
group by uid, id, chapter_name, course_name, play_count_1d, avg_duration_sec_1d, uv_count_1d;
"


dws_user_age_order_nd="
insert overwrite table dws_user_age_order_nd partition (dt='$do_date')
select
    age_group,
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)) order_count_7d,
    sum(order_count_1d) order_count_30d
from (
         select dt,
                age_group,
                CAST(order_count_1d AS BIGINT) order_count_1d
         from dws_user_age_order_1d
         where dt>=date_add('$do_date',-29)
     )t1
group by age_group;
"

dws_trade_user_order_nd="
insert overwrite table dws_trade_user_order_nd partition(dt='$do_date')
select
    user_id,
    min(dt) login_date_first,
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_origin_amount_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_coupon_reduce_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_final_amount_1d,0)),
    sum(order_count_1d),
    sum(order_origin_amount_1d),
    sum(order_origin_amount_1d),
    sum(order_final_amount_1d)
from dws_trade_user_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by user_id;
"

dws_trade_user_payment_nd="
insert overwrite table dws_trade_user_payment_nd partition (dt = '$do_date')
select user_id,
       min(dt) login_date_first,
       sum(if(dt >= date_add('$do_date', -6), payment_count_1d, 0)),
       sum(if(dt >= date_add('$do_date', -6), payment_num_1d, 0)),
       sum(if(dt >= date_add('$do_date', -6), payment_amount_1d, 0)),
       sum(payment_count_1d),
       sum(payment_num_1d),
       sum(payment_amount_1d)
from dws_trade_user_payment_1d
where dt >= date_add('$do_date', -29)
  and dt <= '$do_date'
group by user_id;


"

dws_trade_user_cart_add_nd="
INSERT OVERWRITE TABLE dws_trade_user_cart_add_nd PARTITION (dt = '$do_date')
SELECT user_id,
       sum(if(dt > date_sub('$do_date',7),cart_add_count_1d,0)) cart_add_count_7d,
       sum(cart_add_count_1d) cart_add_count_30d
FROM dws_trade_user_cart_add_1d
         where dt>=date_add('$do_date',-29)
	and dt<='$do_date'
GROUP BY user_id;
"

dws_traffic_page_visitor_page_view_nd="
INSERT OVERWRITE TABLE dws_traffic_page_visitor_page_view_nd PARTITION (dt = '$do_date')
SELECT
       mid_id,
       page_id,
       sum(if(dt >date_sub('$do_date',7),during_time_1d,0)) during_time_7d ,
       sum(during_time_1d) during_time_30d ,
       sum(if(dt >date_sub('$do_date',7),view_count_1d,0)) view_count_7d,
       sum(view_count_1d) view_count_30d
FROM  dws_traffic_page_visitor_page_view_1d
         where dt>=date_add('$do_date',-29)
	    and dt<='$do_date'
GROUP BY mid_id,page_id;
"


dws_course_order_nd="
	insert overwrite table dws_course_order_nd partition (dt='do_date')
	select
		t1.course_id,
		t1.course_name,
		order_count_1d,
		order_original_amount_1d,
		activity_reduce_amount_1d,
		order_total_amount_1d,
		user_count_1d,order_count_7d,
		order_original_amount_7d,
		activity_reduce_amount_7d,
		order_total_amount_7d,
		user_count_7d,
		order_count_30d,
		order_original_amount_30d,
		activity_reduce_amount_30d,
		order_total_amount_30d,
		user_count_30d
	from (select
			course_id,
			course_name,
			count(distinct(order_id)) order_count_1d,
			sum(origin_amount ) order_original_amount_1d,
			sum(coupon_reduce ) activity_reduce_amount_1d,
			sum(final_amount  ) order_total_amount_1d,
			count(distinct user_id) user_count_1d
		from dwd_trade_order_detail_inc
		where dt='$do_date'
		group by course_id,course_name) t1
			left join (select
							course_id,
							sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)) order_count_7d,
							sum(if(dt>=date_add('$do_date',-6),order_original_amount_1d,0)) order_original_amount_7d,
							sum(if(dt>=date_add('$do_date',-6),activity_reduce_amount_1d,0)) activity_reduce_amount_7d,
							sum(if(dt>=date_add('$do_date',-6),order_total_amount_1d,0)) order_total_amount_7d,
							sum(if(dt>=date_add('$do_date',-6),user_count_1d,0)) user_count_7d,
							sum(order_count_1d) order_count_30d,
							sum(order_original_amount_1d ) order_original_amount_30d,
							sum(activity_reduce_amount_1d ) activity_reduce_amount_30d,
							sum(order_total_amount_1d  ) order_total_amount_30d,
							sum(user_count_1d) user_count_30d
						from dws_course_order_1d
						where dt>=date_add('$do_date',-29)
						group by course_id
	) t2
					on  t1.course_id=t2.course_id;
"
case $1 in
"dws_paper_score_page_nd")
	hive --database edu -e "$dws_paper_score_page_nd"
	;;
"dws_question_accuracy_nd")
	hive --database edu -e "$dws_question_accuracy_nd"
	;;
"dws_trade_province_order_nd")
	hive --database edu -e "$dws_trade_province_order_nd"
	;;
"dws_trade_all_order_nd")
	hive --database edu -e "$dws_trade_all_order_nd"
	;;
"dws_trffic_category_user_videoplay_nd")
	hive --database edu -e "$dws_trffic_category_user_videoplay_nd"
	;;
"dws_trffic_subject_user_videoplay_nd")
	hive --database edu -e "$dws_trffic_subject_user_videoplay_nd"
	;;
"dws_trffic_course_user_videoplay_nd")
	hive --database edu -e "$dws_trffic_course_user_videoplay_nd"
	;;
"dws_trade_course_category_order_nd")
	hive --database edu -e "$dws_trade_course_category_order_nd"
	;;
"dws_trade_subject_course_order_nd")
	hive --database edu -e "$dws_trade_subject_course_order_nd"
	;;
"dws_play_user_course_count_nd")
	hive --database edu -e "$dws_play_user_course_count_nd"
	;;
"dws_play_user_chapter_count_nd")
	hive --database edu -e "$dws_play_user_chapter_count_nd"
	;;
"dws_user_age_order_nd")
	hive --database edu -e "$dws_user_age_order_nd"
	;;
"dws_trade_user_order_nd")
	hive --database edu -e "$dws_trade_user_order_nd"
	;;
"dws_trade_user_payment_nd")
	hive --database edu -e "$dws_trade_user_payment_nd"
	;;
"dws_trade_user_cart_add_nd")
	hive --database edu -e "$dws_trade_user_cart_add_nd"
	;;
"dws_traffic_page_visitor_page_view_nd")
	hive --database edu -e "$dws_traffic_page_visitor_page_view_nd"
	;;
"dws_course_order_nd")
	hive --database edu -e "$dws_course_order_nd"
	;;

"all")
	hive --database edu -e "$dws_paper_score_page_nd$dws_question_accuracy_nd$dws_trade_province_order_nd$dws_trade_all_order_nd$dws_trffic_category_user_videoplay_nd$dws_trffic_subject_user_videoplay_nd$dws_trffic_course_user_videoplay_nd$dws_trade_course_category_order_nd$dws_trade_subject_course_order_nd$dws_play_user_course_count_nd$dws_play_user_chapter_count_nd$dws_user_user_login_td$dws_user_age_order_nd$dws_trade_user_order_nd$dws_trade_user_payment_nd$dws_trade_user_cart_add_nd$dws_traffic_page_visitor_page_view_nd$dws_course_order_nd"
	;;
esac